/***********************************************************************************************************************************
 * Name:	AddPushSubscription.sql
 * Author:	Frank Figearo (frank.figearo@us.bbaaviation.com|frank@sqlnerd.me)
 * Summary:	Add a push subscription to an existing publication.
 */
SET IMPLICIT_TRANSACTIONS OFF; IF 0 < @@TRANCOUNT ROLLBACK;
GO
CREATE PROCEDURE #AddPushSubscription (@Publication SYSNAME, @Subscriber SYSNAME, @Subscriber_DB SYSNAME, @Job_Login NVARCHAR(257), @Job_Password SYSNAME) AS
  EXECUTE sp_addsubscription
	@publication				= @publication,
	@subscriber					= @subscriber,
	@destination_db				= N'CPA',
	@subscription_type			= N'Push',
	@sync_type					= N'automatic',
	@article					= N'all',
	@update_mode				= N'read only',
	@subscriber_type			= 0;
  EXECUTE sp_addpushsubscription_agent
	@publication				= @publication,
	@subscriber					= @Subscriber,
	@subscriber_db				= @subscriber_db,
	@job_login					= @job_login,
	@job_password				= @job_password,
	@subscriber_security_mode	= 1,
	@frequency_type				= 64,
	@frequency_interval			= 1,
	@frequency_relative_interval= 1,
	@frequency_recurrence_factor= 0,
	@frequency_subday			= 4,
	@frequency_subday_interval	= 5,
	@active_start_time_of_day	= 0,
	@active_end_time_of_day		= 235959,
	@active_start_date			= 0,
	@active_end_date			= 0,
	@dts_package_location		= N'Distributor';
GO
CREATE PROCEDURE #DropAllSubscriptions (@Publication SYSNAME) AS
  EXECUTE sp_dropsubscription
	@publication	= @Publication,
	@article		= N'all',
	@subscriber		= N'all',
	@destination_db	= N'all';
GO